package tn.softcom.dfbuilder;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Utils {

	private static String databaseUrl;
	private static Connection conn;

	public static Workbook getWorkbook(File file) {
		try {
			if ("xls".equalsIgnoreCase(FilenameUtils.getExtension(file
					.getName())))
				return new HSSFWorkbook(new FileInputStream(file));
			else if ("xlsx".equalsIgnoreCase(FilenameUtils.getExtension(file
					.getName())))
				return new XSSFWorkbook(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void setConnectionParam(HttpServletRequest request) {
		// databaseUrl = "jdbc:Access:/"+ request.getRealPath("") +
		// "/dump/dump.mdb";
		databaseUrl = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="
				+ request.getRealPath("") + "/conf/dump/dump.mdb;";
	}

	public static String getDatabaseUrl() {
		return databaseUrl;
	}

	public static Connection getConnection() throws SQLException,
			ClassNotFoundException {
		if (conn == null) {
			// Class.forName("com.hxtt.sql.access.AccessDriver");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			System.out
					.println("*****************************************************************");
			System.out.println(databaseUrl);
			System.out
					.println("*****************************************************************");
			conn = DriverManager.getConnection(databaseUrl);
		}
		return conn;
	}

	public static double distance(double latitudeX, double longitudeX,
			double latitudeY, double longitudeY) {
		return Math.acos(Math.sin(Math.toRadians(latitudeX))
				* Math.sin(Math.toRadians(latitudeY))
				+ Math.cos(Math.toRadians(latitudeX))
				* Math.cos(Math.toRadians(latitudeY))
				* Math.cos(Math.toRadians(longitudeX - longitudeY))) * 6371;
	}

	

}
